package model.tablemodel;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import util.SortUtils;
import database.DBConnection;

public class ArtikalTableModel extends AbstractTableModel {

	private static final long serialVersionUID = 1L;

	public ArtikalTableModel(Object[] colNames, int rowCount) {
		super(colNames, rowCount);
		basicQuery = "SELECT SIFRA_GRUPE, SIFRA_ARTIKLA, PAKOVANJE, JEDINICA_MERE, NAZIV_ARTIKLA FROM ARTIKAL";
		orderBy = " ORDER BY SIFRA_GRUPE, SIFRA_ARTIKLA";
	}

	@Override
	protected void fillData(String sql) throws SQLException {
		// TODO Auto-generated method stub
		setRowCount(0);
		Statement stmt = DBConnection.getConnection().createStatement();
		ResultSet rset = stmt.executeQuery(sql);
		while (rset.next()) {
			String sifraGrupe = rset.getString("SIFRA_GRUPE");
			String sifraArtikla = rset.getString("SIFRA_ARTIKLA");
			String pakovanje = rset.getString("PAKOVANJE");
			String jedinicaMere = rset.getString("JEDINICA_MERE");
			String nazivArtikla = rset.getString("NAZIV_ARTIKLA");
			addRow(new String[] { sifraGrupe, sifraArtikla, pakovanje,
					jedinicaMere, nazivArtikla });
		}
		rset.close();
		stmt.close();
		fireTableDataChanged();

	}

	@Override
	public void deleteRow(int index) throws SQLException {
		// TODO Auto-generated method stub
		PreparedStatement stmt = DBConnection.getConnection().prepareStatement(
				"DELETE FROM ARTIKAL WHERE SIFRA_GRUPE=? AND SIFRA_ARTIKLA=?");
		String sifraGrupe = (String) getValueAt(index, 0);
		String sifraArtikla = (String) getValueAt(index, 1);
		stmt.setString(1, sifraGrupe);
		stmt.setString(2, sifraArtikla);
		// Brisanje iz baze
		int rowsAffected = stmt.executeUpdate();
		stmt.close();
		DBConnection.getConnection().commit();
		if (rowsAffected > 0) {
			// i brisanje iz TableModel-a
			removeRow(index);
			fireTableDataChanged();
		}
	}

	@Override
	protected int sortedInsert(String[] cols) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
		int left = 0;
		int right = getRowCount() - 1;
		int mid = (left + right) / 2;
		while (left <= right) {
			mid = (left + right) / 2;
			String aSifra = (String) getValueAt(mid, 0);
			if (SortUtils.getLatCyrCollator().compare(cols[0], aSifra) > 0)
				left = mid + 1;
			else if (SortUtils.getLatCyrCollator().compare(cols[0], aSifra) < 0)
				right = mid - 1;
			else {
				// ako su jednaki: to ne moze da se desi ako tabela ima primarni
				// kljuc
				aSifra = (String) getValueAt(mid, 1);
				if (SortUtils.getLatCyrCollator().compare(cols[1], aSifra) > 0)
					left = mid + 1;
				else if (SortUtils.getLatCyrCollator().compare(cols[1], aSifra) < 0)
					right = mid - 1;
				else
					break;
			}
		}
		insertRow(left, cols);
		return left;
	}

	@Override
	public int insertRow(String[] cols) throws SQLException {
		int retVal = 0;
		PreparedStatement stmt = DBConnection
				.getConnection()
				.prepareStatement(
						"INSERT INTO ARTIKAL (SIFRA_GRUPE, SIFRA_ARTIKLA, PAKOVANJE, "
								+ "JEDINICA_MERE, NAZIV_ARTIKLA) VALUES (? ,? ,? ,? ,?)");
		stmt.setString(1, cols[0]);
		stmt.setString(2, cols[1]);
		stmt.setString(3, cols[2]);
		stmt.setString(4, cols[3]);
		stmt.setString(5, cols[4]);
		int rowsAffected = stmt.executeUpdate();
		stmt.close();
		// Unos sloga u bazu
		DBConnection.getConnection().commit();
		if (rowsAffected > 0) {
			// i unos u TableModel
			retVal = sortedInsert(cols);
			fireTableDataChanged();
		}
		return retVal;
	}

	@Override
	public void searchRows(String[] criteria) throws SQLException {
		// TODO Auto-generated method stub
		setRowCount(0);

		boolean first = true;

		if (!criteria[0].equals("")) {
			whereStmt += " WHERE SIFRA_GRUPE LIKE '" + criteria[0] + "%'";
			first = false;
		}

		if (!criteria[1].equals("")) {
			if (first) {
				whereStmt += " WHERE SIFRA_ARTIKLA LIKE '" + criteria[1] + "%'";
				first = false;
			} else {
				whereStmt += " AND SIFRA_ARTIKLA LIKE '" + criteria[1] + "%'";
			}
		}

		if (!criteria[2].equals("")) {
			if (first) {
				whereStmt += " WHERE PAKOVANJE LIKE '" + criteria[2] + "%'";
				first = false;
			} else {
				whereStmt += " AND PAKOVANJE LIKE '" + criteria[2] + "%'";
			}
		}

		if (!criteria[3].equals("")) {
			if (first) {
				whereStmt += " WHERE JEDINICA_MERE LIKE '" + criteria[3] + "%'";
				first = false;
			} else {
				whereStmt += " AND JEDINICA_MERE LIKE '" + criteria[3] + "%'";
			}
		}

		if (!criteria[4].equals("")) {
			if (first) {
				whereStmt += " WHERE NAZIV_ARTIKLA LIKE '" + criteria[4] + "%'";
				first = false;
			} else {
				whereStmt += " AND NAZIV_ARTIKLA LIKE '" + criteria[4] + "%'";
			}
		}

		String sql = basicQuery + whereStmt + orderBy;

		Statement stmt = DBConnection.getConnection().createStatement();
		ResultSet rset = stmt.executeQuery(sql);
		while (rset.next()) {
			String sifraGrupe = rset.getString("SIFRA_GRUPE");
			String sifraArtikla = rset.getString("SIFRA_ARTIKLA");
			String pakovanje = rset.getString("PAKOVANJE");
			String jedinicaMere = rset.getString("JEDINICA_MERE");
			String nazivArtikla = rset.getString("NAZIV_ARTIKLA");
			addRow(new String[] { sifraGrupe, sifraArtikla, pakovanje,
					jedinicaMere, nazivArtikla });
		}

		whereStmt = "";

		rset.close();
		stmt.close();
		fireTableDataChanged();

	}

	@Override
	public int editRow(String[] cols, int index) throws SQLException {
		// TODO Auto-generated method stub
		int retVal = 0;

		PreparedStatement stmt = DBConnection.getConnection().prepareStatement(
				"UPDATE ARTIKAL SET SIFRA_GRUPE = '" + cols[0]
						+ "', SIFRA_ARTIKLA = '" + cols[1] + "', PAKOVANJE = '"
						+ cols[2] + "', JEDINICA_MERE = '" + cols[3]
						+ "', NAZIV_ARTIKLA = '" + cols[4]
						+ "' WHERE SIFRA_GRUPE = '" + getValueAt(index, 0)
						+ "' AND SIFRA_ARTIKLA = '" + getValueAt(index, 1)
						+ "'");
		int rowsAffected = stmt.executeUpdate();
		stmt.close();
		// Unos sloga u bazu
		DBConnection.getConnection().commit();
		if (rowsAffected > 0) {
			// i unos u TableModel
			removeRow(index);
			retVal = sortedInsert(cols);
			fireTableDataChanged();
		}

		return retVal;
	}

	@Override
	protected boolean checkRow(int index) {
		// TODO Auto-generated method stub
		return false;
	}

}
